package ua.nure.nguyen.web.db.dao.h2;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import ua.nure.nguyen.web.db.dao.UserDAO;
import ua.nure.nguyen.web.db.entity.User;

public class H2UserDAO implements UserDAO {

	private static final String DB_DRIVER 	= "org.h2.Driver";
	private static final String DB_CONN_URL = "jdbc:h2:tcp://localhost/~/test";
	private static final String DB_USERNAME = "sa";
	private static final String DB_PASSWORD = "vietpr0";
	
	private static final String COLUMN_ID 		= "ID";
	private static final String COLUMN_HASH 	= "HASH";
	private static final String COLUMN_SALT 	= "SALT";
	private static final String COLUMN_EMAIL 	= "EMAIL";
	private static final String COLUMN_BIRTHDAY = "BIRTHDAY";
	private static final String COLUMN_SEX 		= "SEX";

	private static final String createStm = "INSERT INTO USER(USERNAME, HASH, SALT, EMAIL, BIRTHDAY, SEX) VALUES (?, ?, ?, ?, ?, ?)";
	private static final int CREATE_INDEX_USERNAME 	= 1;
	private static final int CREATE_INDEX_HASH 		= 2;
	private static final int CREATE_INDEX_SALT 		= 3;
	private static final int CREATE_INDEX_EMAIL 	= 4;
	private static final int CREATE_INDEX_BIRTHDAY 	= 5;
	private static final int CREATE_INDEX_SEX 		= 6;
	
	private static final String readStm = "SELECT * FROM USER WHERE USERNAME = ?";
	private static final int READ_INDEX_USERNAME 	= 1;
	
	private static final String updateStm = "UPDATE USER SET USERNAME = ?, HASH = ?, SALT = ?, EMAIL = ?, BIRTHDAY = ?, SEX = ? WHERE ID = ?";
	private static final int UPDATE_INDEX_USERNAME 	= 1;
	private static final int UPDATE_INDEX_HASH 		= 2;
	private static final int UPDATE_INDEX_SALT 		= 3;
	private static final int UPDATE_INDEX_EMAIL 	= 4;
	private static final int UPDATE_INDEX_BIRTHDAY 	= 5;
	private static final int UPDATE_INDEX_SEX 		= 6;
	private static final int UPDATE_INDEX_ID 		= 7;
	
	private static final String deleteStm = "DELETE FROM USER WHERE ID = ?";
	private static final int DELETE_INDEX_ID 		= 1;

	private Connection conn;
	private PreparedStatement ps;

	public H2UserDAO() throws ClassNotFoundException, SQLException {
		Class.forName(DB_DRIVER);
		conn = DriverManager.getConnection(DB_CONN_URL, DB_USERNAME, DB_PASSWORD);
	}

	public long create(User user) throws SQLException {
		long id = -1;
		if (read(user.getUsername()) == null) {
			ps = conn.prepareStatement(createStm);
			ps.setString(CREATE_INDEX_USERNAME, user.getUsername());
			ps.setBytes(CREATE_INDEX_HASH, user.getHash());
			ps.setBytes(CREATE_INDEX_SALT, user.getSalt());
			ps.setString(CREATE_INDEX_EMAIL, user.getEmail());
			ps.setDate(CREATE_INDEX_BIRTHDAY, user.getBirthday());
			ps.setByte(CREATE_INDEX_SEX, user.getSex());
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				id = rs.getLong(1);
			}
			ps = null;
		}
		return id;
	}

	public User read(String username) throws SQLException {
		User user = null;
		ps = conn.prepareStatement(readStm);
		ps.setString(READ_INDEX_USERNAME, username);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		if (rs.next()) {
			long id = rs.getLong(COLUMN_ID);
			byte[] hash = rs.getBytes(COLUMN_HASH);
			byte[] salt = rs.getBytes(COLUMN_SALT);
			String email = rs.getString(COLUMN_EMAIL);
			Date birthday = rs.getDate(COLUMN_BIRTHDAY);
			byte sex = rs.getByte(COLUMN_SEX);
			user = new User(id, username, hash, salt, email, birthday, sex);
		}
		return user;
	}

	public boolean update(User user) throws SQLException {
		ps = conn.prepareStatement(updateStm);
		ps.setString(UPDATE_INDEX_USERNAME, user.getUsername());
		ps.setBytes(UPDATE_INDEX_HASH, user.getHash());
		ps.setBytes(UPDATE_INDEX_SALT, user.getSalt());
		ps.setString(UPDATE_INDEX_EMAIL, user.getEmail());
		ps.setDate(UPDATE_INDEX_BIRTHDAY, user.getBirthday());
		ps.setByte(UPDATE_INDEX_SEX, user.getSex());
		ps.setLong(UPDATE_INDEX_ID, user.getId());
		int result = ps.executeUpdate();
		return result > 0;
	}

	public boolean delete(long id) throws SQLException {
		ps = conn.prepareStatement(deleteStm);
		ps.setLong(DELETE_INDEX_ID, id);
		int result = ps.executeUpdate();
		return result > 0;
	}

}
